CREATE TABLE jms_dm.dm_sqs_loss_network_cnt_dt (
 network_type int(11) NULL COMMENT "网点类型:1总部,2代理区,3加盟商,4中心,5集散点,6网点",
 network_code varchar(100) NULL COMMENT "网点编码 只有普通网点code和代理区",
 date_time date NULL COMMENT "日期",
 loss_type varchar(100) NULL DEFAULT "" COMMENT "遗失类型：遗失、疑似遗失",
 network_name varchar(100) NULL COMMENT "网点名称",
 manage_code varchar(100) NULL COMMENT "大区code",
 manage_name varchar(100) NULL COMMENT "大区name",
 virt_code varchar(100) NULL COMMENT "虚拟代理区code",
 virt_name varchar(100) NULL COMMENT "虚拟代理区name",
 financial_center_code varchar(100) NULL COMMENT "代理区code",
 financial_center_name varchar(100) NULL COMMENT "代理区名称",
 is_org_virt tinyint(4) NULL COMMENT "是否是实际虚拟代理区 1是0否",
 waybill_cnt decimal(16, 2) NULL COMMENT "票量",
 operate_cnt int NULL COMMENT "操作量",
 provider_id varchar(120) NULL COMMENT "省id",
 provider_desc varchar(120) NULL COMMENT "省id",
 city_id varchar(120) NULL COMMENT "城市ID",
 city_desc varchar(120) NULL COMMENT "城市",
 area_id varchar(120) NULL COMMENT "区/县id",
 area_desc varchar(120) NULL COMMENT "区/县id",
 district_code varchar(120) NULL COMMENT "片区code",
 district_name varchar(120) NULL COMMENT "片区name"
) ENGINE=OLAP 
DUPLICATE KEY(network_type, network_code)
COMMENT "遗失率组织统计"
PARTITION BY RANGE(date_time)
   START ("2023-03-02") END ("2023-05-01") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(network_code) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);

alter table  jms_dm.dm_sqs_loss_network_cnt_dt add column(
     manager_area_id varchar(64) comment'网管片区_责任网点id'
    ,manager_area_code  varchar(64) comment'网管片区_责任网点code'
    ,manager_area_name  varchar(128) comment'网管片区_责任网点name'
) ;

